package com.hnkywl.sts.dao.htgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.htgl.Cght;
import com.hnkywl.sts.entity.htgl.Htmx;
import com.hnkywl.sts.entity.jhgl.Bgjhhz;
import com.hnkywl.sts.entity.organ.Qyjg;
import com.hnkywl.sts.entity.xsgl.Jfmx;
import com.hnkywl.sts.service.sys.YgService;
import com.hnkywl.sts.util.DateUtil;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-4-8
 * Time: 下午1:33
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class CghtDao extends SimpleHibernateTemplate<Cght, Long> {
    public Page<Cght> findCgHtList(String code, String zt, String cglx, String htbh, Long ssgsId, Long ssbmId, Long cgrId, Long hzfId, String qdr, Date startDate, Date endDate, String xhtbm, int pageNo, int pageSize) {
        Page<Cght> page = new Page<Cght>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");

        DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Htmx.class);

        detachedCriteria.createAlias("wzgg", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("ht.id").as("ht.id")))
                .add(Restrictions.like("wzgg.oldcode", code, MatchMode.START));

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("hzf", "hzf", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfgs", "wlfgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfbm", "wlfbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfry", "wlfry", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("htwb", "htwb", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("htwb.id").as("htwb_id"))
                        .add(Projections.sqlProjection("htqdr as htqdr", new String[]{"htqdr"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("this_.type as type", new String[]{"type"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("id").as("id"))
                        .add(Projections.sqlProjection("htbm as htbm", new String[]{"htbm"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("hzf.qc").as("hzf_qc"))
                        .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("sbgs.qc").as("sbgs_qc"))
                        .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                        .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Property.forName("zbcg").as("zbcg"))
                        .add(Property.forName("wlfgs.jc").as("wlfgs_jc"))
                        .add(Property.forName("wlfbm.qc").as("wlfbm_qc"))
                        .add(Property.forName("wlfry.xm").as("wlfry_xm"))
                        .add(Projections.sqlProjection("this_.qdrq as qdrq", new String[]{"qdrq"}, new Type[]{Hibernate.DATE}))
                        .add(Property.forName("jsrq").as("jsrq"))
                        .add(Property.forName("wlyl").as("wlyl"))
                        .add(Property.forName("ckzt").as("ckzt"))
                        .add(Property.forName("htms").as("htms"))
                        .add(Property.forName("extension").as("extension"))
                        .add(Projections.sqlProjection("this_.xsxt_htbm as xsxt_htbm", new String[]{"xsxt_htbm"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("processInstanceId").as("processInstanceId"))
                        .add(Projections.sqlProjection("this_.cglx as cglx", new String[]{"cglx"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(code)) {
            criteria.add(Property.forName("id").in(detachedCriteria));
        }
        if (StringUtils.isNotEmpty(cglx)) {
            criteria.add(Restrictions.eq("cglx", cglx));
        } else {
            criteria.add(Restrictions.ne("cglx", "bght"));
        }
        if (StringUtils.isNotEmpty(zt)) {
            criteria.add(Restrictions.eq("zt", zt));
        }
        if (StringUtils.isNotEmpty(xhtbm)) {
            criteria.add(Property.forName("xsxt_htbm").like(xhtbm, MatchMode.ANYWHERE));
        }

        if (startDate != null) {
            criteria.add(Restrictions.ge("qdrq", sDaysAfterNowDate(startDate)));
        }

        if (endDate != null) {
            criteria.add(Restrictions.le("qdrq", jDaysAfterNowDate(endDate)));
        }

        if (StringUtils.isNotEmpty(htbh)) {
            criteria.add(Property.forName("htbm").like(htbh, MatchMode.START));
        }

        if (ssgsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", ssgsId));
        }
        if (ssbmId != null) {
            criteria.add(Restrictions.eq("sbbm.id", ssbmId));
        }
        if (cgrId != null) {
            criteria.add(Restrictions.eq("sbr.id", cgrId));
        }
        if (StringUtils.isNotEmpty(qdr)) {
            criteria.add(Restrictions.like("htqdr", qdr, MatchMode.START));
        }
        if (hzfId != null) {
            criteria.add(Restrictions.eq("hzf.id", hzfId));
        }

        createDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    //处理合同的统配业务，无法使用通用的方法
    public void createDataRegion(Criteria criteria) {
        criteria.createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN);
        switch (YgService.getCurrentYgCxfw()) {
            case 0:
                break;
            case 1:
                criteria.add(Restrictions.or(Restrictions.eq("createOrgan.id", YgService.getCurrentYgZgGsId()), Restrictions.eq("tcbm.id", YgService.getCurrentYgGsId())));
                break;
            case 2:
                criteria.add(Restrictions.or(Restrictions.eq("createDepartment.id", YgService.getCurrentYgGsId()), Restrictions.eq("tcbm.id", YgService.getCurrentYgGsId())));
                break;
            case 3:
                criteria.add(Restrictions.eq("createDepartment.id", YgService.getCurrentYgGsId()));
                criteria.add(Restrictions.eq("createUser.id", YgService.getCurrentYg().getId()));
                break;
        }
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    private Date sDaysAfterNowDate(int n) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DAY_OF_MONTH, -n);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    public Page findCgHtList(Long wzjhid, String zt, String htType, String gysmc, int pageNo, int pageSize) {
        Page<Cght> page = new Page<Cght>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("hzf", "hzf", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfgs", "wlfgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfbm", "wlfbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfry", "wlfry", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lj", "lj", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("htbm").as("htbm"))
                        .add(Property.forName("ll").as("ll"))
                        .add(Property.forName("lj.mc").as("lj_mc"))
                        .add(Property.forName("lj.ll").as("lj_ll"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("rzlx").as("rzlx"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("hzf.id").as("hzf_id"))
                        .add(Property.forName("hzf.jc").as("hzf_jc"))
                        .add(Property.forName("jhsj").as("jhsj"))
                        .add(Property.forName("jhdd").as("jhdd"))
                        .add(Property.forName("sl").as("sl"))
                        .add(Property.forName("hsze").as("hsze"))
                        .add(Property.forName("htms").as("htms"))
                        .add(Property.forName("sbgs.id").as("sbgs_id"))
                        .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                        .add(Property.forName("sbbm.id").as("sbbm_id"))
                        .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                        .add(Property.forName("sbr.id").as("sbr_id"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Property.forName("wlfgs.id").as("wlfgs_id"))
                        .add(Property.forName("wlfgs.jc").as("wlfgs_jc"))
                        .add(Property.forName("wlfbm.id").as("wlfbm_id"))
                        .add(Property.forName("wlfbm.qc").as("wlfbm_qc"))
                        .add(Property.forName("wlfry.id").as("wlfry_id"))
                        .add(Property.forName("wlfry.xm").as("wlfry_xm"))
                        .add(Property.forName("qdrq").as("qdrq"))
                        .add(Property.forName("jsrq").as("jsrq"))
                        .add(Projections.sqlProjection("this_.cglx as cglx", new String[]{"cglx"}, new Type[]{Hibernate.STRING})))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

        criteria.add(Restrictions.eq("zt", "ybc"));
        return this.findByCriteria(page, criteria);
    }

    public Page findCgHtList(Long wzjhid, String zt, List<Long> ids, String htType, String gysmc, int pageNo, int pageSize) {
        Page<Cght> page = new Page<Cght>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("hzf", "hzf", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfgs", "wlfgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfbm", "wlfbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfry", "wlfry", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lj", "lj", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("htbm").as("htbm"))
                        .add(Property.forName("ll").as("ll"))
                        .add(Property.forName("lj.mc").as("lj_mc"))
                        .add(Property.forName("lj.ll").as("lj_ll"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("rzlx").as("rzlx"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("hzf.id").as("hzf_id"))
                        .add(Property.forName("hzf.jc").as("hzf_jc"))
                        .add(Property.forName("jhsj").as("jhsj"))
                        .add(Property.forName("jhdd").as("jhdd"))
                        .add(Property.forName("sl").as("sl"))
                        .add(Property.forName("hsze").as("hsze"))
                        .add(Property.forName("sbgs.id").as("sbgs_id"))
                        .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                        .add(Property.forName("sbbm.id").as("sbbm_id"))
                        .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                        .add(Property.forName("sbr.id").as("sbr_id"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Property.forName("wlfgs.id").as("wlfgs_id"))
                        .add(Property.forName("wlfgs.jc").as("wlfgs_jc"))
                        .add(Property.forName("wlfbm.id").as("wlfbm_id"))
                        .add(Property.forName("wlfbm.qc").as("wlfbm_qc"))
                        .add(Property.forName("wlfry.id").as("wlfry_id"))
                        .add(Property.forName("wlfry.xm").as("wlfry_xm"))
                        .add(Property.forName("qdrq").as("qdrq"))
                        .add(Property.forName("jsrq").as("jsrq"))
                        .add(Projections.sqlProjection("this_.cglx as cglx", new String[]{"cglx"}, new Type[]{Hibernate.STRING})))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (wzjhid != null)
            criteria.add(Restrictions.eq("wzjh.id", wzjhid));
        if (StringUtils.isNotEmpty(zt)) {
            criteria.add(Restrictions.eq("zt", zt));
        } else {
            criteria.add(Restrictions.or(Restrictions.eq("zt", "ybc"), Restrictions.eq("zt", "ysp")));
        }

        criteria.add(Restrictions.eq("bzjfs", 2));

        if (StringUtils.isNotEmpty(gysmc))
            criteria.add(Restrictions.like("hzf.qc", "%" + gysmc + "%"));

        if (StringUtils.isNotEmpty(htType)) {
            if (htType.equals("cgrk")) {
                criteria.add(Restrictions.or(Restrictions.eq("cglx", "cgrk"), Restrictions.eq("cglx", "zddh")));
            } else {
                criteria.add(Restrictions.eq("cglx", htType));
            }

            if (!"tpcght".equals(htType)) {
                RegionDao.createDataRegion(criteria);
            }
        } else {
            RegionDao.createDataRegion(criteria);
        }


        return this.findByCriteria(page, criteria);
    }


    public Page<Map> findjzjsCgHtList(int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();

        List<String> condition = new ArrayList<String>();
        String sql = "   select            distinct ht.id \"id\",\n" +
                "                         ht.htbm as \"htbm\", \n" +
                "                         (select  NVL(sum(t.SBSL*t.HSJ),0) from T_BIZ_HTMX t where t.HT_ID=ht.id and t.deleted!=1) as \"hsze\", \n" +
                "                         gs.qc as \"hzf_jc\", \n" +
                "                         ht.yfje as \"yfje\", \n" +
                "                          sbgs.jc as \"sbgs_jc\",\n" +
                "                         to_char(ht.qdrq,'yyyy/mm/dd') as \"qdrq\",\n" +
                "                         jy.order_sn as  \"order_sn\", \n" +
                "                         jy.is_online_payment as \"is_online_payment\"  \n" +
                "                         \n" +
                "                     from t_biz_ht  ht  \n" +
                "                  left join t_biz_jydd jy  on jy.order_id=ht.orderid \n" +
                "                  left join t_sys_gsjg gs on gs.id= ht.hzf_id \n" +
                "                  left join t_sys_gsjg sbgs on sbgs.id=ht.sbgs_id\n" +
                "                  where ht.type='cght'and jy.is_online_payment=0 ";


     /*   if (StringUtils.isNotEmpty(htbm)) {
            condition.add(" ht.htbm like :htbm ");
            param.put("htbm", "%"+htbm.trim()+"%");
        }

        if (org.apache.commons.lang3.StringUtils.isNotEmpty(htbm)) {
            condition.add(" ht.htbm like :htbm ");
            param.put("htbm", "%"+htbm + "%");
        }*/

        //String whereSql = "";
        //if (!condition.isEmpty())
        //  whereSql = " and " + org.apache.commons.lang3.StringUtils.join(condition, " and ");
        String countSql = "select count(1) from (" + sql + ") where 1=1 ";
        String resultSql = sql;

        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }

        return page;
    }


    public Page findCghtListForZjBzj(List<Long> ids, Long wzjhid, String zt, String htType, String gysmc, int pageNo, int pageSize) {
        Page<Cght> page = new Page<Cght>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("hzf", "hzf", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfgs", "wlfgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfbm", "wlfbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfry", "wlfry", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lj", "lj", CriteriaSpecification.LEFT_JOIN)
//                .createAlias("zddhxsht", "zddhxsht", CriteriaSpecification.LEFT_JOIN)
//                .createAlias("zddhxsht.hzf", "zddhxsht_hzf", CriteriaSpecification.LEFT_JOIN)
//                .createAlias("zddhxsht.gc", "zddhxsht_gc", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
//                        .add(Property.forName("id").as("id"))
                        .add(Projections.sqlProjection("this_.id as id", new String[]{"id"}, new Type[]{Hibernate.LONG}))
                        .add(Property.forName("htbm").as("htbm"))
                        .add(Property.forName("ll").as("ll"))
                        .add(Property.forName("lj.id").as("lj_id"))
                        .add(Property.forName("lj.mc").as("lj_mc"))
                        .add(Property.forName("lj.ll").as("lj_ll"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("rzlx").as("rzlx"))
//                        .add(Property.forName("zddhxsht.id").as("xsht_id"))
//                        .add(Property.forName("zddhxsht.htbm").as("xsht_htbm"))
//                        .add(Property.forName("zddhxsht_gc.id").as("xsht_gc_id"))
//                        .add(Property.forName("zddhxsht_hzf.id").as("xsht_hzf_id"))
//                        .add(Property.forName("zddhxsht_hzf.qc").as("xsht_hzf_qc"))
//                        .add(Property.forName("zddhxsht_hzf.qc").as("xsht_hzf_jc"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("hzf.id").as("hzf_id"))
                        .add(Property.forName("hzf.jc").as("hzf_jc"))
                        .add(Property.forName("jhsj").as("jhsj"))
                        .add(Property.forName("jhdd").as("jhdd"))
                        .add(Property.forName("sl").as("sl"))
                        .add(Property.forName("hsze").as("hsze"))
                        .add(Property.forName("htms").as("htms"))
                        .add(Property.forName("sbgs.id").as("sbgs_id"))
                        .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                        .add(Property.forName("sbbm.id").as("sbbm_id"))
                        .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                        .add(Property.forName("sbr.id").as("sbr_id"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Property.forName("wlfgs.id").as("wlfgs_id"))
                        .add(Property.forName("wlfgs.jc").as("wlfgs_jc"))
                        .add(Property.forName("wlfbm.id").as("wlfbm_id"))
                        .add(Property.forName("wlfbm.qc").as("wlfbm_qc"))
                        .add(Property.forName("wlfry.id").as("wlfry_id"))
                        .add(Property.forName("wlfry.xm").as("wlfry_xm"))
                        .add(Property.forName("qdrq").as("qdrq"))
                        .add(Property.forName("jsrq").as("jsrq"))
                        .add(Projections.sqlProjection("this_.cglx as cglx", new String[]{"cglx"}, new Type[]{Hibernate.STRING})))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (wzjhid != null)
            criteria.add(Restrictions.eq("wzjh.id", wzjhid));
        if (StringUtils.isNotEmpty(zt))
            criteria.add(Restrictions.eq("zt", zt));
        if (StringUtils.isNotEmpty(gysmc))
            criteria.add(Restrictions.like("hzf.qc", "%" + gysmc + "%"));

        criteria.add(Restrictions.in("id", ids));

        if (StringUtils.isNotEmpty(htType)) {
            if (htType.equals("cgrk")) {
                criteria.add(Restrictions.or(Restrictions.eq("cglx", "cgrk"), Restrictions.eq("cglx", "zddh")));
            } else {
                criteria.add(Restrictions.eq("cglx", htType));
            }

            if (!"tpcght".equals(htType)) {
                RegionDao.createDataRegion(criteria);
            }
        } else {
            RegionDao.createDataRegion(criteria);
        }


        return this.findByCriteria(page, criteria);
    }

    //保供采购单选择保供合同
    public Map findBgCgHtList(Long htid, String zt, String htType, int pageNo, int pageSize) {
//        Page<Cght> page = new Page<Cght>();
//        page.setAutoCount(true);
//        page.setPageNo(1);
//        page.setPageSize(-1);
//        page.setOrder("desc");
//        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("hzf", "hzf", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfgs", "wlfgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfbm", "wlfbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfry", "wlfry", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lj", "lj", CriteriaSpecification.LEFT_JOIN)
//                .createAlias("zddhxsht", "zddhxsht", CriteriaSpecification.LEFT_JOIN)
//                .createAlias("zddhxsht.hzf", "zddhxsht_hzf", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Projections.sqlProjection("this_.id as id", new String[]{"id"}, new Type[]{Hibernate.LONG}))
                        .add(Property.forName("htbm").as("htbm"))
                        .add(Property.forName("ll").as("ll"))
                        .add(Property.forName("lj.id").as("lj_id"))
                        .add(Property.forName("lj.mc").as("lj_mc"))
                        .add(Property.forName("lj.ll").as("lj_ll"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("rzlx").as("rzlx"))
//                        .add(Property.forName("zddhxsht.id").as("xsht_id"))
//                        .add(Property.forName("zddhxsht.htbm").as("xsht_htbm"))
//                        .add(Property.forName("zddhxsht_hzf.id").as("xsht_hzf_id"))
//                        .add(Property.forName("zddhxsht_hzf.qc").as("xsht_hzf_qc"))
//                        .add(Property.forName("zddhxsht_hzf.qc").as("xsht_hzf_jc"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("hzf.id").as("hzf_id"))
                        .add(Property.forName("hzf.jc").as("hzf_jc"))
                        .add(Property.forName("jhsj").as("jhsj"))
                        .add(Property.forName("jhdd").as("jhdd"))
                        .add(Property.forName("sl").as("sl"))
                        .add(Property.forName("sbgs.id").as("sbgs_id"))
                        .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                        .add(Property.forName("sbbm.id").as("sbbm_id"))
                        .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                        .add(Property.forName("sbr.id").as("sbr_id"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Property.forName("wlfgs.id").as("wlfgs_id"))
                        .add(Property.forName("wlfgs.jc").as("wlfgs_jc"))
                        .add(Property.forName("wlfbm.id").as("wlfbm_id"))
                        .add(Property.forName("wlfbm.qc").as("wlfbm_qc"))
                        .add(Property.forName("wlfry.id").as("wlfry_id"))
                        .add(Property.forName("wlfry.xm").as("wlfry_xm"))
                        .add(Property.forName("qdrq").as("qdrq"))
                        .add(Property.forName("jsrq").as("jsrq"))
                        .add(Projections.sqlProjection("this_.cglx as cglx", new String[]{"cglx"}, new Type[]{Hibernate.STRING})))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (htid != null)
            criteria.add(Restrictions.eq("id", htid));
//        if (StringUtils.isNotEmpty(zt))
//            criteria.add(Restrictions.eq("zt", zt));
        Map object = (Map) criteria.uniqueResult();
        return object;
    }

    public List<Map> findCgHtmxContent(Long cghtId) {
        Criteria criteria = getSession().createCriteria(Htmx.class)
                .createAlias("wzgg", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("wzgg.id").as("wzgg.id"))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.oldcode").as("wzgg.oldcode"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
                        .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                        .add(Projections.sqlProjection("wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
//                        .add(Projections.sqlProjection("wzgg1_.cz||' '||wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck").as("ck"))
                        .add(Property.forName("sbjs").as("sbjs"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("ph").as("ph"))
                        .add(Property.forName("jthsjsl").as("jthsjsl"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("hsje").as("hsje"))
                        .add(Property.forName("bz").as("bz"))
                        .add(Property.forName("xsxt_xsdh").as("xsxt_xsdh"))
                        .add(Property.forName("spec").as("spec"))
                        .add(Projections.sqlProjection("mxh as \"mxh\"", new String[]{"mxh"}, new Type[]{Hibernate.LONG}))
                ).add(Restrictions.eq("ht.id", cghtId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("mxh").asc());
        return criteria.list();
    }

    public List<Map> findCgHtmxContentForCgd(Long cghtId) {
        Criteria criteria = getSession().createCriteria(Htmx.class)
                .createAlias("wzgg", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("wzgg.id").as("wzgg.id"))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.oldcode").as("wzgg.oldcode"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
                        .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                        .add(Projections.sqlProjection("wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
//                        .add(Projections.sqlProjection("wzgg1_.cz||' '||wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck").as("ck"))
                        .add(Property.forName("sbjs").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("ph").as("ph"))
                        .add(Property.forName("jthsjsl").as("jthsjsl"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("hsje").as("hsje"))
                        .add(Property.forName("bz").as("bz"))
                        .add(Projections.sqlProjection("mxh as \"mxh\"", new String[]{"mxh"}, new Type[]{Hibernate.LONG}))
                ).add(Restrictions.eq("ht.id", cghtId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("mxh").asc());
        return criteria.list();
    }

    //保供采购单选择合同明细
    public List<Map> findBgCgHtmxContent(Long cghtId, List<Long> htmxIds) {
        Criteria criteria = getSession().createCriteria(Htmx.class)
                .createAlias("wzgg", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Projections.distinct(Projections.sqlProjection("wzgg1_.id as wzgg_id", new String[]{"wzgg_id"}, new Type[]{Hibernate.LONG})))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.oldcode").as("wzgg.oldcode"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
                        .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                        .add(Projections.sqlProjection("wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
//                        .add(Projections.sqlProjection("wzgg1_.cz||' '||wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck").as("ck"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("ph").as("ph"))
                        .add(Property.forName("jthsjsl").as("jthsjsl"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("hsje").as("hsje"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("ht.id", cghtId)).add(Restrictions.in("id", htmxIds)).addOrder(Property.forName("wzgg.oldcode").asc())
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("wzgg.code").asc());
        return criteria.list();
    }

    public Page findCgHtmxList(Long cghtId, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("id");
        Criteria criteria = getSession().createCriteria(Htmx.class)
                .createAlias("wzgg", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("wzgg.id").as("wzgg.id"))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.oldcode").as("wzgg.oldcode"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
//                        .add(Property.forName("wzgg.ggxh").as("wzgg.ggxh"))
                        .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                        .add(Projections.sqlProjection("wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
//                        .add(Projections.sqlProjection("wzgg1_.cz||' '||wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck").as("ck"))
                        .add(Property.forName("sbjs").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("ph").as("ph"))
                        .add(Property.forName("jthsjsl").as("jthsjsl"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("hsje").as("hsje"))
                        .add(Property.forName("bz").as("bz"))
                        .add(Property.forName("xsxt_xsdh").as("xsxt_xsdh"))
                        .add(Property.forName("spec").as("spec"))
                ).add(Restrictions.eq("ht.id", cghtId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("mxh").asc());
        return this.findByCriteria(page, criteria);
    }

    //采购合同明细合计
    public Map findXshtHj(Long cghtId) {
        String sql = "select sum(sbsl) as \"sbsl\",sum(hsje) as \"hsje\" from t_biz_htmx where ht_id = ?";
        return (Map) createSQLQuery(sql, cghtId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    public void deleteHtmx(Long htId) {
        String hql = "update Htmx set deleted=1 where ht.id=?";
        Query query = this.getSession().createQuery(hql);
        query.setLong(0, htId);
        query.executeUpdate();
    }


    public Page<Map> findBgjhCght(Date startDate, Date endDate, String wzdl, String wzbm, Long jhdwId, String htbm, Long gysid, int pageNo, int pageSize) {
        Calendar calendar = Calendar.getInstance();
        //得到月初
        calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMinimum(Calendar.DAY_OF_MONTH));
        Date sysdate = calendar.getTime();
        Calendar cal = new GregorianCalendar();
        cal.setTime(sysdate);
        cal.add(calendar.DATE, -2);//把日期往后增加一天.整数往后推,负数往前移动
        Date MonthDate = cal.getTime();
        //得到月末
        calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        Date datetime = calendar.getTime();

        String yggl = StringUtils.EMPTY;
        if (existsSQLQuery("select 1 from t_sys_yg_bmfw where yg_id = ?", YgService.getCurrentYg().getId())) {
            yggl = " inner join v_wzbm_gg_yg_search bmfw  on a.wzgg_id = bmfw.ggid and bmfw.yg_id=" + YgService.getCurrentYg().getId();
            //where = " bmfw.yg_id = " + YgService.getCurrentYg().getId();
        } else {
            yggl = " inner join v_wzbm_gg_search bmfw on a.wzgg_id = bmfw.ggid and bmfw.gsid=" + YgService.getCurrentYgGsId();
        }

        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();


        String countsql = "select count(c.hzmxid)" +
                " from (select a.wzgg_id, a.cgfs, a.id as hzmxid, a.zt, a.bz,a.dhrq, a.sbsl, a.deleted, hz.jhsj, hz.id as hzid," +
                " hz.sm, a.jhsl, hz.dhck_id, hz.jhdw_id, a.createuser_id, bmfw.ggid as ggid, bmfw.oldcode as ggcode, bmfw.name as ggname,bmfw.pm_name," +
                " bmfw.ggxh  as ggxh,bmfw.dw  as ggdw, bmfw.th as ggth from T_BIZ_BGJHHZMX a inner join T_BIZ_BGJHHZ hz  on a.bgjhhz_id = hz.id" +
                " and hz.deleted = 0 and hz.jhsj >= :dhrq1 and hz.jhsj < :dhrq2" + yggl + ") c" +
                " left join (select t.id htid, t.htbm, t.hzf_id,t.drsj, t.htqdr, mx.id mxid, mx.wz_id, nvl(dz.detailid, 0) detailid" +
                " from T_BIZ_HT t inner join T_BIZ_HTMX mx  on t.id = mx.ht_id and mx.deleted = 0 left join T_BIZ_HTJHDZ dz on mx.id = dz.bghtmxid" +
                "  where t.cglx = 'bght' and t.deleted = 0 and t.zt = 'ysp' and t.qdrq<=:currentDate and t.jsrq>=:currentDate) b on c.wzgg_id = b.wz_id" +
                "  and (c.cgfs = 1 and c.hzmxid = b.detailid or c.cgfs = 2)" +
                " where c.zt = 7 and c.deleted = 0 and c.sbsl-nvl((select sum(case when d.yrk=1 then m.rksl else m.sbsl end)  " +
                " from t_biz_jhcgddz z, t_biz_cgdmx m, t_biz_cgd d where z.hzmxid = c.hzmxid and z.cgdmxid = m.id and m.cgd_id = d.id and m.deleted = 0), 0)>0";

        String sql = "select c.hzmxid as \"hzmxid\",c.bz as \"bz\",c.cgfs as \"cgfs\",c.dhrq as \"dhrq\",c.jhsj as \"jhsj\",b.htid as \"htid\",b.htbm  as \"htbm\",b.htqdr as \"htqdr\",b.drsj as \"htdrsj\",b.mxid as \"htmxid\",b.wz_id as \"wzid\",c.hzid as \"hzid\"," +
                "c.sm as \"hzsm\",c.ggid as \"ggid\",c.ggcode as \"ggcode\",c.ggname as \"ggname\",c.ggxh as \"ggxh\",c.ggdw as \"ggdw\",c.ggth as \"ggth\",c.jhsl as \"jhsl\", " +
                "c.dhck_id as \"dhck_id\",(select ck.mc  from T_SYS_CK ck where ck.id = c.dhck_id) as \"ckmc\", " +
                "b.hzf_id  as \"gysid\",(select gs.qc from t_sys_gsjg gs where gs.id=b.hzf_id)  as \"gysmc\", " +
                "c.sbsl-nvl((select sum(case when d.yrk=1 then m.rksl else m.sbsl end) " +
                " from t_biz_jhcgddz z, t_biz_cgdmx m, t_biz_cgd d where z.hzmxid = c.hzmxid and z.cgdmxid = m.id and m.cgd_id = d.id and m.deleted = 0), 0) as \"wcgsl\"," +
                " (select jg.jc from T_SYS_GSJG jg where jg.id = c.jhdw_id) as \"jhdw\"," +
                " (select yg.xm from t_sys_yg yg where yg.id = c.createuser_id) as \"jhyxm\"" +
                " from (select a.wzgg_id, a.cgfs, a.id as hzmxid, a.zt, a.bz,a.dhrq, a.sbsl, a.deleted, hz.jhsj, hz.id as hzid," +
                " hz.sm, a.jhsl, hz.dhck_id, hz.jhdw_id, a.createuser_id, bmfw.ggid as ggid, bmfw.oldcode as ggcode, bmfw.name as ggname,bmfw.pm_name," +
                " bmfw.ggxh  as ggxh,bmfw.dw  as ggdw, bmfw.th as ggth from T_BIZ_BGJHHZMX a inner join T_BIZ_BGJHHZ hz  on a.bgjhhz_id = hz.id" +
                " and hz.deleted = 0 and hz.jhsj >= :dhrq1 and hz.jhsj < :dhrq2" + yggl + ") c" +
                " left join (select t.id htid, t.htbm, t.hzf_id,t.drsj, t.htqdr, mx.id mxid, mx.wz_id, nvl(dz.detailid, 0) detailid" +
                " from T_BIZ_HT t inner join T_BIZ_HTMX mx  on t.id = mx.ht_id and mx.deleted = 0 left join T_BIZ_HTJHDZ dz on mx.id = dz.bghtmxid" +
                "  where t.cglx = 'bght' and t.deleted = 0 and t.zt = 'ysp'  and t.qdrq<=:currentDate and t.jsrq>=:currentDate) b on c.wzgg_id = b.wz_id" +
                "  and (c.cgfs = 1 and c.hzmxid = b.detailid or c.cgfs = 2)" +
                " where c.zt = 7 and c.deleted = 0 and c.sbsl-nvl((select sum(case when d.yrk=1 then m.rksl else m.sbsl end)  " +
                " from t_biz_jhcgddz z, t_biz_cgdmx m, t_biz_cgd d where z.hzmxid = c.hzmxid and z.cgdmxid = m.id and m.cgd_id = d.id and m.deleted = 0), 0)>0";

//        condition.add(" and" + where);

        String ordersql = " order by c.hzid desc,c.hzmxid asc";

        param.put("currentDate", new Date());

        if (startDate != null) {
            param.put("dhrq1", startDate);
        } else {
            param.put("dhrq1", MonthDate);
        }
        if (endDate != null) {
            param.put("dhrq2", endDate);
        } else {
            param.put("dhrq2", datetime);
        }
        if (StringUtils.isNotEmpty(wzbm)) {
            condition.add(" ( c.ggcode like :oldcode) ");
            param.put("oldcode", wzbm + "%");
        }
        if (StringUtils.isNotEmpty(wzdl)) {
            condition.add(" c.pm_name in (select t.name from T_SYS_PM t,T_SYS_RJWZBM r where  t.RJWZBM_ID = r.id and r.yjwzbm_id = :wzdl and r.deleted!=1 and t.deleted!=1) ");
            param.put("wzdl", wzdl);
        }
        if (jhdwId != null) {
            condition.add(" c.jhdw_id=:jhdwId ");
            param.put("jhdwId", jhdwId);
        }
        if (htbm != null && StringUtils.isNotEmpty(htbm)) {
            condition.add(" b.htbm like :htbm ");
            param.put("htbm", "%" + htbm.trim() + "%");
        }
        if (gysid != null) {
            condition.add(" b.hzf_id=:gysid ");
            param.put("gysid", gysid);
        }
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and" + StringUtils.join(condition, " and ");
        String resultSql = sql + whereSql + ordersql;
        BigDecimal count = (BigDecimal) createSQLQuery(countsql + whereSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 根据合同编号查询保供合同是否存在
     *
     * @param htbm
     * @return
     */
    public Cght findBght(String htbm) {
        String hql = "from Cght where cglx = 'bght' and zt!='yzz' and htbm = ?";
        List<Cght> cghtList = find(hql, htbm);
        if (cghtList != null && cghtList.size() > 0) {
            return cghtList.get(0);
        } else {
            return null;
        }
    }

    public Cght findCghtByHtbm(String htbm) {
        String hql = "from Cght where htbm = ?";
        List<Cght> cghtList = find(hql, htbm.trim());
        if (cghtList != null && cghtList.size() > 0) {
            return cghtList.get(0);
        } else {
            return null;
        }
    }


    public List<Map> findHtmxList(Long cghtId) {
        String sql = " select \n" +
                "     jtwz.code as \"code\",\n" +
                "     jtwz.name as \"name\",\n" +
                "     jtwz.ggxh as \"ggxh\",\n" +
                "     zd.z as \"cd\",\n" +
                "     mx.sbsl as \"sl\",\n" +
                "     zdz.z as \"jzfs\",\n" +
                "     mx.hsj as \"hsj\",\n" +
                "     mx.bhsj as \"bhsj\" ,\n" +
                "     mx.hsje as \"hsje\",\n" +
                "     ht.bz as \"bz\",\n" +
                "     bzj.tx as \"tx\",\n" +
                "     bzj.txl as \"txl\",\n" +
                "      bzj.bzjbl as \"bzjll\",\n" +
                "      bzj.je  as \"bzjje\"\n" +
                "      from  t_biz_ht  ht  \n" +
                "      left join  t_biz_htmx mx  on mx.ht_id=ht.id\n" +
                "      left join  t_sys_gg jtwz  on jtwz.id=mx.wz_id\n" +
                "      left join t_sys_sjzdz zd  on zd.id=mx.sjzdz_cd_id\n" +
                "      left join t_sys_sjzdz zdz  on zdz.id=mx.sjzdz_jzfs_id\n" +
                "      left join  t_biz_bzj  bzj  on bzj.ht_id=ht.id\n" +
                "      \n" +
                "      where  ht.id=?";
        return (List<Map>) this.createSQLQuery(sql, cghtId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
    }


    public List<Map> findbzjList(Long cghtId) {
        String sql = " select   ht.htbm as \"htbm\",\n" +
                "                            bzj.bzjbl \"bzjbl\",\n" +
                "                            sk.sfje \"skje\",\n" +
                "                            sk.bm \"bm\",\n" +
                "                           (select  NVL(sum(t.SBSL*t.HSJ),0) from T_BIZ_HTMX t where t.HT_ID=?)*bzj.bzjbl/100 as \"bzjje\"\n" +
                "                            from t_biz_bzj bzj  \n" +
                "                            left join t_biz_ht  ht on ht.id=bzj.ht_id\n" +
                "                            left join t_biz_htmx mx on mx.ht_id=ht.id\n" +
                "                            left join t_biz_sfk sk on sk.id=bzj.sk_id\n" +
                " \n" +
                "                            where ht.id=?";
        return (List<Map>) this.createSQLQuery(sql, cghtId, cghtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    public List<Map> findbzj(Long cghtId) {
        String sql = "  select \n" +
                "               bzj.bzjbl as \"bzjbl\",\n" +
                "               bzj.je \"je\",\n" +
                "                bzj.llje as \"llje\"\n" +
                "          \n" +
                "          \n" +
                "           from t_biz_bzj bzj left join t_biz_ht ht on ht.id =bzj.ht_id\n" +
                "            where  ht.id=?";
        return (List<Map>) this.createSQLQuery(sql, cghtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    public Map findSumWlyFromCght(Long cghtId) {
        String sql = "select NVL(sum(a.SBSL),0) wly from T_BIZ_CGD t left join T_BIZ_CGDMX a on a.CGD_ID=t.ID where t.CGHT_ID = ? and t.ZT='ysp' and t.DELETED!=1";
        return (Map) createSQLQuery(sql, cghtId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    public Map findSumHtslFromCght(Long cghtId) {
        String sql = "select sum(sbsl) htsl from t_biz_htmx where deleted = 0 and ht_id = ?";
        return (Map) createSQLQuery(sql, cghtId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    public Cght findCghtByXsxtXsdh(String xsdh) {
        String sql = "select cght.id from t_biz_ht cght,t_biz_htmx mx where mx.ht_id = cght.id and cght.type= 'cght' and mx.xsxt_xsdh = ? and cght.zt = 'ysp' and cght.deleted = 0 ";
        List<Map> list = (List<Map>) this.createSQLQuery(sql, xsdh).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        Cght cght = new Cght();
        if (list.size() > 0) {
            cght = this.get(Long.valueOf(list.get(0).get("ID").toString()));
        }
        return cght;
    }

    public Cght findMxtsTxl() {
        String hql = "from Cght where to_char(qdrq,'yyyy-MM') = ?";
        List<Cght> list = this.find(hql, DateUtil.getMonthDate(new Date()));
        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }


//    public List findQyjgByCghtId(Long cghtId) {
//        String sql = "select g.bzjfs from t_sys_gsjg g where g.id = (select xy.xy_id from T_LJ_XY xy where xy.lj_id = (select t.lj_id from t_biz_ht t where t.id = ?))";
//        List<Map> list = (List<Map>) this.createSQLQuery(sql, cghtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
//       // Qyjg qyjg=new Qyjg();
//       //  return  qyjg;
//        return list;
//    }

    public void updateCghtFromXs(Integer mxts, Double txl, Double yhbl,Date qdrq,Date fksj,Date jhsj,Long id) {
        String sql = "update t_biz_ht set  mxts = ?,txl = ?,yhbl = ?,qdrq= ?,fksj= ?,jhsj=?  where id = ?";
        this.executeSQL(sql, mxts, txl, yhbl,qdrq,fksj,jhsj, id);
    }
    public Cght findByCghtId(Long id) {
        String hql = " from Cght where id = ? ";
        return this.find(hql, id).size() <= 0 ? null : (Cght) this.find(hql, id).get(0);
    }
}
